import requests
import os
from xml.etree import ElementTree as ET
from openpyxl import workbook

base_dar = os.path.dirname(os.path.abspath(__file__))
new_file = os.path.join(base_dar,  'weather.xlsx')
# 创建excel并且删除默认Sheet
wb = workbook.Workbook()
del wb['Sheet']
while True:
    city = input("请输入城市（Q/q退出）：")
    if city.upper() == "Q":
        break
    url = "http://ws.webxml.com.cn/WebServices/WeatherWebService.asmx/getWeatherbyCityName?theCityName={0}".format(city)
    res = requests.get(url=url)
    print(res.text)
    root = ET.XML(res.text)

    sheet = wb.create_sheet(city)
    for row_index, node in enumerate(root, 1):
        text = node.text
        cell = sheet.cell(row_index, 1)
        cell.value = text
        wb.save(new_file)